package rc.database.RegNumber;

/**
 * Created by IntelliJ IDEA.
 * User: Slejus
 * Date: 13.6.11
 * Time: 13.12
 * To change this template use File | Settings | File Templates.
 */

import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.extensions.webscripts.AbstractWebScript;
import org.springframework.extensions.webscripts.WebScriptRequest;
import org.springframework.extensions.webscripts.WebScriptResponse;
import rc.database.connection.DBOperations;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class RegNumberWebScript extends AbstractWebScript {

    private final String PSSEQ = "prasymu_skundu_seq";
    private final String SGSEQ = "skundu_gincu_seq";
    private final String KOMSEQ = "komandiruociu_klaus_seq";
    private final String PERSEQ = "personalo_klaus_seq";
    private final String NTPSSEQ = "nt_prasymu_skundu_seq";
    private final String SIUNSEQ = "siunciamu_dokumentu_seq";
    private final String ISAKSEQ = "isakymo_reg_num_seq";
    private final String ATOSSEQ = "atostogu_klaus_seq";
    private final String GAUTUSEQ = "gautu_dokumentu_seq";
    private final String BUHALSEQ = "buhalteriniu_dokumentu_seq";
    private final String SIUNATSEQ = "siunciamu_atsisakymu_seq";
    private final String MPSEQ = "materialines_pasalp_kl_seq";
    private final String KPKSEQ = "kitais_personalo_kl_seq";
    private final String PAZSEQ = "pazymu_per_klaus_seq";


    public void execute(WebScriptRequest req, WebScriptResponse res) throws IOException {
        ResultSet rs = null;
        Connection conn = null;
        Statement stmt = null;
        String RegNumber = null;
        try {
            ApplicationContext context = new ClassPathXmlApplicationContext("classpath:alfresco/extension/connection-context.xml");
            DBOperations db = (DBOperations)context.getBean("dbOperations"); // DB operations object
            Class.forName(db.getJdbcDriver());
            conn = DriverManager.getConnection(db.getJdbcUrl(), db.getUsername(), db.getPassword());
            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            String group = req.getParameter("group");

            if (group.equals("g")) RegNumber = getCounter(stmt, rs, db, group);
            else if (group.equals("b")) RegNumber = getCounter(stmt, rs, db, group);
            else if (group.equals("sk")) RegNumber = getCounter(stmt, rs, db, group);
            else if (group.equals("vs")) RegNumber = getCounter(stmt, rs, db, group);
            else if (group.equals("r")) RegNumber = getCounter(stmt, rs, db, group);
            else if (group.equals("s")) RegNumber = getCounter(stmt, rs, db, group);
            else if (group.equals("sa")) RegNumber = getCounter(stmt, rs, db, group);
            else if (group.equals("v")) RegNumber = getCounter(stmt, rs, db, group);
            else if (group.equals("a")) RegNumber = getCounter(stmt, rs, db, group);
            else if (group.equals("k")) RegNumber = getCounter(stmt, rs, db, group);
            else if (group.equals("p")) RegNumber = getCounter(stmt, rs, db, group);
            else if (group.equals("pk")) RegNumber = getCounter(stmt, rs, db, group);
            else if (group.equals("mp")) RegNumber = getCounter(stmt, rs, db, group);
            else if (group.equals("pa")) RegNumber = getCounter(stmt, rs, db, group);
            else {RegNumber = null;}

            if (RegNumber != null) {
                JSONObject obj = new JSONObject();
                obj.put("regNumber", RegNumber);
                String jsonString = obj.toString();
                res.setContentType("application/json; charset=utf-8");
                res.getWriter().write(jsonString);
            } else {
                JSONObject obj = new JSONObject();
                obj.put("regNumber", "error");
                String jsonString = obj.toString();
                res.getWriter().write(jsonString);
            }
        } catch (JSONException ex) {
            Logger.getLogger(RegNumberWebScript.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(RegNumberWebScript.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(RegNumberWebScript.class.getName()).log(Level.SEVERE, null, ex);
        }  finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {Logger.getLogger(RegNumberWebScript.class.getName()).log(Level.SEVERE, null, e);}
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {Logger.getLogger(RegNumberWebScript.class.getName()).log(Level.SEVERE, null, e);}
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {Logger.getLogger(RegNumberWebScript.class.getName()).log(Level.SEVERE, null, e);}
            }
        }
    }

    public String getCounter(Statement stmt, ResultSet rs, DBOperations db, String counterGroup) throws ClassNotFoundException, SQLException {
        String regNum = null, counterValue = null;
        if (counterGroup.equals("g")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(GAUTUSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        } else if (counterGroup.equals("b")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(BUHALSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        } else if (counterGroup.equals("sk")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(PSSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        } else if (counterGroup.equals("vs")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(NTPSSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        } else if (counterGroup.equals("r")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(SGSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        } else if (counterGroup.equals("s")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(SIUNSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        } else if (counterGroup.equals("sa")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(SIUNATSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        } else if (counterGroup.equals("v")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(ISAKSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        } else if (counterGroup.equals("a")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(ATOSSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        } else if (counterGroup.equals("k")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(KOMSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        } else if (counterGroup.equals("p")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(PERSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        } else if (counterGroup.equals("pk")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(KPKSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        } else if (counterGroup.equals("mp")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(MPSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        } else if (counterGroup.equals("pa")) {
            rs = stmt.executeQuery(db.getSequenceNexVal(PAZSEQ));
            if (rs.next()) {
                counterValue = Integer.toString(rs.getInt(1));
            }
        }
        regNum = formatNumber(counterValue, counterGroup);
        return regNum;

    }

    public static String formatNumber(String counter, String group) {
        String formatedNumber = null;
        if (group.equals("g")) {
            formatedNumber = "g-" + counter;
        } else if (group.equals("b")) {
            formatedNumber = "b-" + counter;
        } else if (group.equals("sk")) {
            formatedNumber = "sk-" + counter;
        } else if (group.equals("vs")) {
            formatedNumber = "vs-" + counter;
        } else if (group.equals("r")) {
            formatedNumber = "r-" + counter;
        } else if (group.equals("s")) {
            formatedNumber = "(1.1.21.)s-" + counter;
        } else if (group.equals("sa")) {
            formatedNumber = "(1.1.30.)sa-" + counter;
        } else if (group.equals("v")) {
            formatedNumber = "v-" + counter;
        } else if (group.equals("a")) {
            formatedNumber = "a-" + counter;
        } else if (group.equals("k")) {
            formatedNumber = "k-" + counter;
        } else if (group.equals("p")) {
            formatedNumber = "p-" + counter;
        } else if (group.equals("pk")) {
            formatedNumber = "pk-" + counter;
        } else if (group.equals("mp")) {
            formatedNumber = "mp-" + counter;
        } else if (group.equals("pa")) {
            formatedNumber = counter;
        }
        return formatedNumber;
    }
}
